#!/usr/bin/env python
# encoding: utf-8


from PyQt5.QtWidgets import *
from PyQt5.QtGui import *
from PyQt5.QtCore import *

import os
import shutil
import time
import datetime
import xlwt
import xlrd
import xlsxwriter


class TableView(QTableView):
    def __init__(self, parent=None):
        '''表格初始化'''
        super(TableView, self).__init__(parent)
        self.model = QStandardItemModel(0, 0)
        self.HeaderList = []
        # self.HeaderList = ['x(mm)', 'y(mm)', 'z(mm)', '主测通道(mGy)', '监测通道(mGy)']
        # self.model.setHorizontalHeaderLabels(self.HeaderList)  #
        self.setModel(self.model)
        # 下面代码让表格100填满窗口
        self.horizontalHeader().setStretchLastSection(True)
        self.horizontalHeader().setSectionResizeMode(QHeaderView.Stretch)
        self.isClearChooseLine_Flag = False
        #  self.setMaximumHeight(250)

        self.clear_data()
        # 右键菜单设置
        self.setContextMenuPolicy(Qt.CustomContextMenu)
        self.customContextMenuRequested.connect(self.right_menu)

    def right_menu(self):
        self.menu = QMenu()
        exportAction = QAction("&导出excel", triggered=self.exportToExcel)
        self.menu.addAction(exportAction)
        exportRepeatAction = QAction("&导出重复性excel", triggered=self.exportToExcelRepeat)
        self.menu.addAction(exportRepeatAction)
        self.menu.exec(QCursor.pos())

    def exportToExcel(self):
        f = QFileDialog.getSaveFileName(self, "导出Excel", '%s/data' % os.getcwd(), 'Excel Files(*.xls)')
        if f[0]:
            try:
                filename = f[0]
                wb = xlwt.Workbook()
                ws = wb.add_sheet('sheet1')  # sheetname
                data = self.getDataRows()
                if data:
                    for i in range(0, len(data)):
                        for j in range(0, len(data[i])):
                            ws.write(i, j, data[i][j])
                    wb.save(filename)
                    QMessageBox.warning(self, '提示', '导出成功!\r\n%s' % filename, QMessageBox.Yes)
            except:
                QMessageBox.warning(self, '提示', '导出失败!\r\n文档已打开，请关闭文档后再试！', QMessageBox.Yes)

    def exportToExcelRepeat(self):
        """导出excel, 重复性测量"""
        template_path = '%s/data/repeat-template.xlsx'%os.getcwd()
        _time = datetime.datetime.now().strftime("%Y%m%d%H%M%S")
        new_path = '%s/data/%s.xlsx'%(os.getcwd(), _time)
        self.mycopyfile(template_path, new_path)

        data = self.getDataRows()

        workbook = xlsxwriter.Workbook(new_path)
        worksheet = workbook.add_worksheet('Sheet1')

        header_format = workbook.add_format({
            'bold': True,
            'text_wrap': True,
            'align': 'center',
            'fg_color': '#D7E4BC',
            'border': 1})

        ad_format = workbook.add_format({
            'text_wrap': True,
            'align': 'center',
            'fg_color': '#92b2e8',
            'border': 1})
        current_format = workbook.add_format({
            'text_wrap': True,
            'align': 'center',
            'fg_color': '#9099a8',
            'border': 1})
        average_format = workbook.add_format({
            'text_wrap': True,
            'align': 'center',
            'fg_color': '#9099a8',
            'border': 1})
        repeat_format = workbook.add_format({
            'text_wrap': True,
            'align': 'center',
            'fg_color': '#c3cbd8',
            'border': 1})
        headings1 = ['通道1', '电流', 'AD1', 'AD2', 'AD3', 'AD4', 'AD5', 'AD6', 'AD7', 'AD8', 'AD9', 'AD10', '平均AD值', '重复性']

        worksheet.write_row('B5', headings1, header_format )
        temp = []
        row = 6
        for i in range(0,len(data)):
            temp.append(float(data[i][4]))
            if (i+1)%10 == 0:
                worksheet.write('C%s'%row, float(data[i][0].replace('pA','')), current_format)
                worksheet.write_row('D%s'%row, temp, ad_format)
                worksheet.write_formula('N%s'%row, '=AVERAGE(D%s:M%s)'%(row, row), average_format)
                worksheet.write_formula('O%s'%row, '=STDEV(D%s:M%s)/N%s'%(row, row, row) ,repeat_format)

                row += 1
                temp = []

        # Create a new chart object. In this case an embedded chart.
        chart1 = workbook.add_chart({'type': 'line'})

        # Configure the first series.
        chart1.add_series({
            'name':       '=Sheet1!$N$5',
            'categories': '=Sheet1!$N$6:$N$11',
            'values':     '=Sheet1!$C$6:$C$11',
        })

        # Add a chart title and some axis labels.
        chart1.set_title ({'name': '通道1'})
        chart1.set_x_axis({'name': '平均AD'})
        chart1.set_y_axis({'name': '电流(pA)'})

        # Set an Excel chart style. Colors with white outline and shadow.
        #  chart1.set_style(10)

        # Insert the chart into the worksheet (with an offset).
        worksheet.insert_chart('A21', chart1)


        headings2 = ['通道2', '电流', 'AD1', 'AD2', 'AD3', 'AD4', 'AD5', 'AD6', 'AD7', 'AD8', 'AD9', 'AD10', '平均AD值', '重复性']

        worksheet.write_row('B13', headings1, header_format)
        temp = []
        row = 14
        for i in range(0,len(data)):
            temp.append(float(data[i][5]))
            if (i+1)%10 == 0:
                worksheet.write('C%s'%row, float(data[i][0].replace('pA','')), current_format)
                worksheet.write_row('D%s'%row, temp, ad_format)
                worksheet.write_formula('N%s'%row, '=AVERAGE(D%s:M%s)'%(row, row), average_format)
                worksheet.write_formula('O%s'%row, '=STDEV(D%s:M%s)/N%s'%(row, row, row), repeat_format)
                row += 1
                temp = []

        # Create a new chart object. In this case an embedded chart.
        chart2 = workbook.add_chart({'type': 'line'})

        # Configure the first series.
        chart2.add_series({
            'name':       '=Sheet1!$N$5',
            'categories': '=Sheet1!$N$14:$N$19',
            'values':     '=Sheet1!$C$14:$C$19',
        })

        # Add a chart title and some axis labels.
        chart2.set_title ({'name': '通道2'})
        chart2.set_x_axis({'name': '平均AD'})
        chart2.set_y_axis({'name': '电流(pA)'})

        # Set an Excel chart style. Colors with white outline and shadow.
        #  chart1.set_style(10)

        # Insert the chart into the worksheet (with an offset).
        worksheet.insert_chart('J21', chart2)

        workbook.close()

        QMessageBox.warning(self, '提示', '导出excel成功!\r\n%s'%new_path, QMessageBox.Yes)


    def mycopyfile(self, srcfile, dstfile):
        if not os.path.isfile(srcfile):
            print("%s not exist!"%(srcfile))
        else:
            fpath,fname=os.path.split(dstfile)    #分离文件名和路径
            if not os.path.exists(fpath):
                os.makedirs(fpath)                #创建路径
            shutil.copyfile(srcfile,dstfile)      #复制文件
            print("copy %s -> %s"%( srcfile,dstfile))

    def setHeader(self, header=[]):
        self.HeaderList = header
        self.model.setHorizontalHeaderLabels(header)

    def clear_data(self):
        self.model.clear()
        self.model = QStandardItemModel(0, 0)
        self.model.setHorizontalHeaderLabels(self.HeaderList)  #
        self.setModel(self.model)

    def add_data(self, data):
        rowNum = self.model.rowCount()  # 总行数
        self.insert_data(rowNum, data)

    def insert_data(self, rowNum, data):
        for i in range(0, len(data)):
            if isinstance(data[i], int):
                self.model.setItem(rowNum, i, QStandardItem(str('%.3f' % data[i])))
                continue
            if isinstance(data[i], float):
                self.model.setItem(rowNum, i, QStandardItem(str('%.3f' % data[i])))
                continue
            if isinstance(data[i], str):
                self.model.setItem(rowNum, i, QStandardItem(data[i]))
                continue

    def getRowNumber(self):
        return self.tableView.model.rowCount()

    def getDataColumns(self, col_idx=None):
        rowNum = self.model.rowCount()  # 总行数
        colNum = self.model.columnCount()  # 总行数
        if col_idx is None:
            data = []
            for j in range(0, colNum):
                col = [self.model.data(self.model.index(i, j)) for i in range(0, rowNum)]
                data.append(col)
            return data
        else:
            data = [self.model.data(self.model.index(i, col_idx)) for i in range(0, rowNum)]
            return data

    def getDataRows(self, row_idx=None):
        rowNum = self.model.rowCount()  # 总行数
        colNum = self.model.columnCount()  # 总行数
        data = []
        if row_idx is None:
            for i in range(0, rowNum):
                row = [self.model.data(self.model.index(i, j)) for j in range(0, colNum)]
                data.append(row)
            return data
        else:
            data = [self.model.data(self.model.index(row_idx, j)) for j in range(0, colNum)]
            return data

